﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DomainModel;  // must add this...
using System.Configuration; // must add this...
using System.Data.SqlClient; // must add this...
using System.Data; // must add this...

namespace DAL
{
  public static class DALStudent
  {
    static string connection_string = ConfigurationSettings.AppSettings["dsn"];

    public static void InsertStudent(Tv student, ref List<string> errors)
    {
      SqlConnection conn = new SqlConnection(connection_string);
      try
      {
        string strSQL = "spInsertStudentInfo";

        SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
        mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@student_id", SqlDbType.VarChar, 20));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@ssn", SqlDbType.VarChar, 9));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@first_name", SqlDbType.VarChar, 50));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@last_name", SqlDbType.VarChar, 50));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 64));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@shoe_size", SqlDbType.Float));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@weight", SqlDbType.Int));

        mySA.SelectCommand.Parameters["@student_id"].Value = student.id;
        mySA.SelectCommand.Parameters["@ssn"].Value = student.ssn;
        mySA.SelectCommand.Parameters["@first_name"].Value = student.first_name;
        mySA.SelectCommand.Parameters["@last_name"].Value = student.last_name;
        mySA.SelectCommand.Parameters["@email"].Value = student.email;
        mySA.SelectCommand.Parameters["@shoe_size"].Value = student.shoe_size;
        mySA.SelectCommand.Parameters["@weight"].Value = student.weight;

        DataSet myDS = new DataSet();
        mySA.Fill(myDS);

      }
      catch (Exception e)
      {
        errors.Add("Error: " + e.ToString());
      }
      finally
      {
        conn.Dispose();
        conn = null;
      }
    }

    public static void DeleteStudent(string id, ref List<string> errors)
    {
      SqlConnection conn = new SqlConnection(connection_string);

      try
      {
        string strSQL = "spDeleteStudentInfo";

        SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
        mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@student_id", SqlDbType.VarChar, 20));

        mySA.SelectCommand.Parameters["@student_id"].Value = id;

        DataSet myDS = new DataSet();
        mySA.Fill(myDS);

      }
      catch (Exception e)
      {
        errors.Add("Error: " + e.ToString());
      }
      finally
      {
        conn.Dispose();
        conn = null;
      }
    }

    public static Tv GetStudentDetail(string id, ref List<string> errors)
    {
      SqlConnection conn = new SqlConnection(connection_string);
      Tv student = null;

      try
      {
        string strSQL = "spGetStudentInfo";

        SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
        mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@student_id", SqlDbType.VarChar, 20));

        mySA.SelectCommand.Parameters["@student_id"].Value = id;

        DataSet myDS = new DataSet();
        mySA.Fill(myDS);

        if (myDS.Tables[0].Rows.Count == 0)
          return null;

        student = new Tv();
        student.id = myDS.Tables[0].Rows[0]["student_id"].ToString();
        student.first_name = myDS.Tables[0].Rows[0]["first_name"].ToString();
        student.last_name = myDS.Tables[0].Rows[0]["last_name"].ToString();
        student.ssn = myDS.Tables[0].Rows[0]["ssn"].ToString();
        student.email = myDS.Tables[0].Rows[0]["email"].ToString();
        student.shoe_size = (float)Convert.ToDouble(myDS.Tables[0].Rows[0]["shoe_size"].ToString());
        student.weight = Convert.ToInt32(myDS.Tables[0].Rows[0]["weight"].ToString());
      }
      catch (Exception e)
      {
        errors.Add("Error: " + e.ToString());
      }
      finally
      {
        conn.Dispose();
        conn = null;
      }

      return student;
    }

    public static List<Tv> GetStudentList(ref List<string> errors)
    {
      SqlConnection conn = new SqlConnection(connection_string);
      Tv student = null;
      List<Tv> studentList = new List<Tv>();

      try
      {
        string strSQL = "spGetStudentList";

        SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
        mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

        DataSet myDS = new DataSet();
        mySA.Fill(myDS);

        if (myDS.Tables[0].Rows.Count == 0)
          return null;

        for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
        {
          student = new Tv();
          student.id = myDS.Tables[0].Rows[i]["student_id"].ToString();
          student.first_name = myDS.Tables[0].Rows[i]["first_name"].ToString();
          student.last_name = myDS.Tables[0].Rows[i]["last_name"].ToString();
          student.ssn = myDS.Tables[0].Rows[i]["ssn"].ToString();
          student.email = myDS.Tables[0].Rows[i]["email"].ToString();
          student.shoe_size = (float)Convert.ToDouble(myDS.Tables[0].Rows[i]["shoe_size"].ToString());
          student.weight = Convert.ToInt32(myDS.Tables[0].Rows[i]["weight"].ToString());
          studentList.Add(student);
        }
      }
      catch (Exception e)
      {
        errors.Add("Error: " + e.ToString());
      }
      finally
      {
        conn.Dispose();
        conn = null;
      }

      return studentList;
    }
  }
}
